﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using System.Data.SqlClient;
using Entities;
using System.Globalization;

namespace DataAccessLayer
{
    public class KhoaHocDAL : DBConnection
    {
        public List<KhoaHocEntity> getAll()
        {
            List<KhoaHocEntity> lst = new List<KhoaHocEntity>();
            try
            {
                MySqlCommand cmd = new MySqlCommand("khoa_hoc_getAll", con);
                cmd.CommandType = CommandType.StoredProcedure;

                if (con.State != ConnectionState.Open)
                    con.Open();
                MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                while (dr.Read())
                {
                    KhoaHocEntity obj = new KhoaHocEntity();
                    obj.MaKhoaHoc = Convert.ToInt32(dr["MaKhoaHoc"]);
                    obj.TenKhoaHoc = Convert.ToString(dr["TenKhoaHoc"]);
                    obj.MaLopHoc = Convert.ToInt32(dr["MaLopHoc"]);
                    obj.NgayBatDau = Convert.ToDateTime(dr["NgayBatDau"]);
                    obj.NgayKetThuc = Convert.ToDateTime(dr["NgayKetThuc"]);
                    obj.SoBuoi = Convert.ToInt32(dr["SoBuoi"]);
                    obj.GioHoc = TimeSpan.Parse(dr["GioHoc"].ToString());
                    obj.LichHoc = Convert.ToString(dr["LichHoc"]);
                    obj.GhiChu = Convert.ToString(dr["GhiChu"]);
                    lst.Add(obj);
                }
                dr.Close();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return lst;
        }

        public KhoaHocEntity getById(int _id)
        {
            KhoaHocEntity obj = new KhoaHocEntity();
            try
            {
                MySqlCommand cmd = new MySqlCommand("khoa_hoc_getById", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("maKhoaHoc", _id);

                if (con.State != ConnectionState.Open)
                    con.Open();
                MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                if (dr.Read())
                {
                    obj.MaKhoaHoc = Convert.ToInt32(dr["MaKhoaHoc"]);
                    obj.TenKhoaHoc = Convert.ToString(dr["TenKhoaHoc"]);
                    obj.MaLopHoc = Convert.ToInt32(dr["MaLopHoc"]);
                    obj.NgayBatDau = Convert.ToDateTime(dr["NgayBatDau"]);
                    obj.NgayKetThuc = Convert.ToDateTime(dr["NgayKetThuc"]);
                    obj.SoBuoi = Convert.ToInt32(dr["SoBuoi"]);
                    obj.GioHoc = TimeSpan.Parse(dr["GioHoc"].ToString());
                    obj.LichHoc = Convert.ToString(dr["LichHoc"]);
                    obj.GhiChu = Convert.ToString(dr["GhiChu"]);
                }
                dr.Close();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return obj;
        }

        public List<KhoaHocEntity> getByLopHocId(int _id)
        {
            List<KhoaHocEntity> lst = new List<KhoaHocEntity>();
            try
            {
                MySqlCommand cmd = new MySqlCommand("khoa_hoc_getsByLopHocId", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("maLopHoc", _id);

                if (con.State != ConnectionState.Open)
                    con.Open();
                MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                while (dr.Read())
                {
                    KhoaHocEntity obj = new KhoaHocEntity();
                    obj.MaKhoaHoc = Convert.ToInt32(dr["MaKhoaHoc"]);
                    obj.TenKhoaHoc = Convert.ToString(dr["TenKhoaHoc"]);
                    obj.MaLopHoc = Convert.ToInt32(dr["MaLopHoc"]);
                    obj.NgayBatDau = Convert.ToDateTime(dr["NgayBatDau"]);
                    obj.NgayKetThuc = Convert.ToDateTime(dr["NgayKetThuc"]);
                    obj.SoBuoi = Convert.ToInt32(dr["SoBuoi"]);
                    obj.GioHoc = TimeSpan.Parse(dr["GioHoc"].ToString());
                    obj.LichHoc = Convert.ToString(dr["LichHoc"]);
                    obj.GhiChu = Convert.ToString(dr["GhiChu"]);
                    lst.Add(obj);
                }
                dr.Close();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return lst;
        }

        public List<KhoaHocEntity> getByHocVienId(string _id)
        {
            List<KhoaHocEntity> lst = new List<KhoaHocEntity>();
            try
            {
                MySqlCommand cmd = new MySqlCommand("khoa_hoc_getbyHocVienId", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("maHocVien", _id);

                if (con.State != ConnectionState.Open)
                    con.Open();
                MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                while (dr.Read())
                {
                    KhoaHocEntity obj = new KhoaHocEntity();
                    obj.MaKhoaHoc = Convert.ToInt32(dr["MaKhoaHoc"]);
                    obj.TenKhoaHoc = Convert.ToString(dr["TenKhoaHoc"]);
                    obj.MaLopHoc = Convert.ToInt32(dr["MaLopHoc"]);
                    obj.NgayBatDau = Convert.ToDateTime(dr["NgayBatDau"]);
                    obj.NgayKetThuc = Convert.ToDateTime(dr["NgayKetThuc"]);
                    obj.SoBuoi = Convert.ToInt32(dr["SoBuoi"]);
                    obj.GioHoc = TimeSpan.Parse(dr["GioHoc"].ToString());
                    obj.LichHoc = Convert.ToString(dr["LichHoc"]);
                    obj.GhiChu = Convert.ToString(dr["GhiChu"]);
                    lst.Add(obj);
                }
                dr.Close();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return lst;
        }

        public int addKhoaHoc(KhoaHocEntity _obj)
        {
            int result = 0;
            try
            {
                if (_obj.MaKhoaHoc != -1)
                    throw new Exception("Khoa Hoc Da Ton Tai");

                MySqlCommand cmd = new MySqlCommand("khoa_hoc_insert", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("tenKhoaHoc", _obj.TenKhoaHoc);
                cmd.Parameters.AddWithValue("maLopHoc", _obj.MaLopHoc);
                cmd.Parameters.AddWithValue("ngayBatDau", _obj.NgayBatDau);
                cmd.Parameters.AddWithValue("ngayKetThuc", _obj.NgayKetThuc);
                cmd.Parameters.AddWithValue("soBuoi", _obj.SoBuoi);
                cmd.Parameters.AddWithValue("gioHoc", _obj.GioHoc.ToString());
                cmd.Parameters.AddWithValue("lichHoc", _obj.LichHoc);
                cmd.Parameters.AddWithValue("ghiChu", _obj.GhiChu);
                cmd.Parameters.AddWithValue("lastId", MySqlDbType.Int32);
                cmd.Parameters["lastId"].Direction = ParameterDirection.Output;

                if (con.State != ConnectionState.Open)
                    con.Open();
                result = cmd.ExecuteNonQuery();
                int i = Convert.ToInt32(cmd.Parameters["lastId"].Value);
                if (_obj.MaKhoaHoc == -1)
                    _obj.MaKhoaHoc = i;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return result;
        }

        public int deleteKhoaHoc(int _id)
        {
            int result = 0;
            try
            {
                if (_id == -1)
                    return 0;
                MySqlCommand cmd = new MySqlCommand("khoa_hoc_delete", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("maKhoaHoc", _id);
                cmd.Parameters["maKhoaHoc"].Direction = ParameterDirection.Input;

                if (con.State != ConnectionState.Open)
                    con.Open();
                result = cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return result;
        }

        public int updateKhoaHoc(KhoaHocEntity _obj)
        {
            int result = 0;
            try
            {
                if (_obj.MaKhoaHoc == -1)
                    return addKhoaHoc(_obj);

                MySqlCommand cmd = new MySqlCommand("lop_hoc_update", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("maKhoaHoc", _obj.MaKhoaHoc);
                cmd.Parameters.AddWithValue("tenKhoaHoc", _obj.TenKhoaHoc);
                cmd.Parameters.AddWithValue("maLopHoc", _obj.MaLopHoc);
                cmd.Parameters.AddWithValue("ngayBatDau", _obj.NgayBatDau);
                cmd.Parameters.AddWithValue("ngayKetThuc", _obj.NgayKetThuc);
                cmd.Parameters.AddWithValue("soBuoi", _obj.SoBuoi);
                cmd.Parameters.AddWithValue("gioHoc", _obj.GioHoc.ToString());
                cmd.Parameters.AddWithValue("lichHoc", _obj.LichHoc);
                cmd.Parameters.AddWithValue("ghiChu", _obj.GhiChu);

                if (con.State != ConnectionState.Open)
                    con.Open();
                result = cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return result;
        }
    }
}
